<?php
namespace ScarecrowExcel;

use Exception;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * Excel文件操作类
 * @author Scarecrow
 * Class ExcelHandle
 * @package ScarecrowExcel
 */
class ExcelHandle
{
    /**
     * Excel导出
     *
     * @param array  $datas      导出数据，格式['dataList'=>['A1' => 'XXXX公司报表', 'B1' => '序号'], 'imgList'=>['A3'=>'图片在服务器的绝对地址','B3'=>'图片在服务器的绝对地址']]
     * @param string $fileName   导出文件名称
     * @param array  $options    操作选项，例如：
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
     *                           array  setARGB     设置背景色，例如['A1'=>Color::COLOR_BLUE, 'C1'=>Color::COLOR_YELLOW]
     *                           array  setFontARGB  设置字体颜色  例如['A1'=>COLOR_BLUE ]
     *                           const COLOR_BLACK = 'FF000000';
     *                           const COLOR_WHITE = 'FFFFFFFF';
     *                           const COLOR_RED = 'FFFF0000';
     *                           const COLOR_DARKRED = 'FF800000';
     *                           const COLOR_BLUE = 'FF0000FF';
     *                           const COLOR_DARKBLUE = 'FF000080';
     *                           const COLOR_GREEN = 'FF00FF00';
     *                           const COLOR_DARKGREEN = 'FF008000';
     *                           const COLOR_YELLOW = 'FFFFFF00';
     *                           const COLOR_DARKYELLOW = 'FF808000';
     *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
     *                           array  rowHeight   设置行高  例如['1'=>100,'2'=>150]
     *                           array  setWrapText 设置文本自动换行  例如['A1','A2']
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式，整列设置，例如['A' => 'General']
     *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
     *                           array  bold        设置加粗样式，例如['A1', 'A2']
     *                           array  fontSize    设置单元格字体大小['A1'=>10,'A2'=>'20']
     *                           array  fontName    设置单元格字体['A1'=>'Arial']
     *                           string title       设置当前表格的标题 例：['title'=>'Scarecrow']
     *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
     * @param $exportType 导出类型 Xlsc/Xls/Csv
     * @return bool
     */
    public static function exportExcel($dataInfo,  $fileName = '', $options = [], $exportType="Xlsx")
    {
        try {
            if (!array_key_exists('dataList', $dataInfo) && !array_key_exists('imageDatas', $dataInfo)) {
                $datas = $dataInfo;
                $imageDatas = [];
            } else {
                $datas = array_key_exists('dataList', $dataInfo) ? $dataInfo['dataList'] : [];
                $imageDatas = array_key_exists('imgList', $dataInfo) ? $dataInfo['imgList'] : [];
            }

            if (empty($datas) && empty($imageDatas)) {
                return ['code'=>500,'msg'=>'导出数据不能为空', 'data'=>[]];
            }

            $slowExportType = strtolower($exportType);
            if (!in_array($slowExportType, ['xls', 'xlsx', 'csv'])) {
                return ['code'=>501,'msg'=>'导出数据文件类型不支持', 'data'=>[]];
            }

            $exportType = ucfirst(strtolower($exportType));

            set_time_limit(0);
            ini_set('memory_limit', '1024M');
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = new Spreadsheet();
            /* 设置默认文字居左，上下居中 */
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
            ];
            $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
            /* 设置Excel Sheet */
            $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

            if (isset($options['title']) && $options['title']) {
                $activeSheet->setTitle($options['title']);
                unset($options['title']);
            }

            /* 打印设置 */
            if (isset($options['print']) && $options['print']) {
                /* 设置打印为A4效果 */
                $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                /* 设置打印时边距 */
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()->setTop($pValue / 2);
                $activeSheet->getPageMargins()->setBottom($pValue * 2);
                $activeSheet->getPageMargins()->setLeft($pValue / 2);
                $activeSheet->getPageMargins()->setRight($pValue / 2);
            }

            /* 行数据处理 */
            foreach ($datas as $sKey => $sItem) {
                /* 默认文本格式 */
                $pDataType = DataType::TYPE_STRING;

                /* 设置单元格格式 */
                if (isset($options['format']) && !empty($options['format'])) {
                    $colRow = Coordinate::coordinateFromString($sKey);

                    /* 存在该列格式并且有特殊格式 */
                    if (isset($options['format'][$colRow[0]]) &&
                        NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                        $activeSheet->getStyle($sKey)->getNumberFormat()
                            ->setFormatCode($options['format'][$colRow[0]]);

                        if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
                            is_numeric(str_replace(['￥', ','], '', $sItem))) {
                            /* 数字格式转换为数字单元格 */
                            $pDataType = DataType::TYPE_NUMERIC;
                            $sItem     = str_replace(['￥', ','], '', $sItem);
                        }
                    } elseif (is_int($sItem)) {
                        $pDataType = DataType::TYPE_NUMERIC;
                    }
                }

                $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);

                /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
                if (false !== strstr($sKey, ":")) {
                    $options['mergeCells'][$sKey] = $sKey;
                }
            }
            unset($datas);

            if ($imageDatas) {
                $extList = ['jpg','jpeg','gif', 'bmp', 'png', 'pdf', 'doc', 'docx', 'xlsx', 'xls'];
                foreach ($imageDatas as $iKey => $iItem) {
                    if (!file_exists($iItem)) {
                        continue;
                    }
                    if (!in_array(strtolower(pathinfo($iItem, PATHINFO_EXTENSION)), $extList)) {
                        continue;
                    }
                    $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                    $drawing ->setName($iKey);
                    $drawing ->setPath($iItem);
                    $drawing ->setWidth(60);
                    $drawing ->setHeight(30);
                    $drawing ->setCoordinates($iKey);
                    $drawing ->setOffsetX(0);
                    $drawing ->setOffsetY(0);
                    $drawing ->setWorksheet($activeSheet);
                }
            }
            unset($imageDatas);

            /* 设置锁定行 */
            if (isset($options['freezePane']) && !empty($options['freezePane'])) {
                $activeSheet->freezePane($options['freezePane']);
                unset($options['freezePane']);
            }

            /* 设置宽度 */
            if (isset($options['setWidth']) && !empty($options['setWidth'])) {
                foreach ($options['setWidth'] as $swKey => $swItem) {
                    $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
                }

                unset($options['setWidth']);
            }

            /*设置行高*/
            if (isset($options['rowHeight']) && !empty($options['rowHeight'])) {
                foreach ($options['rowHeight'] as $swKey => $swItem) {
                    $activeSheet->getRowDimension($swKey)->setRowHeight($swItem);
                }

                unset($options['rowHeight']);

            }

            /* 设置背景色 */
            if (isset($options['setARGB']) && !empty($options['setARGB'])) {
                foreach ($options['setARGB'] as $sItem  =>  $color) {
                    $activeSheet->getStyle($sItem)
                        ->getFill()->setFillType(Fill::FILL_SOLID)
                        ->getStartColor()->setARGB($color);
                }

                unset($options['setARGB']);
            }

            /* 设置字体颜色*/
            if (isset($options['setFontARGB']) && !empty($options['setFontARGB'])) {
                foreach ($options['setFontARGB'] as $sItem  =>  $color) {
                    $activeSheet->getStyle($sItem)
                        ->getFont()->getColor()->setARGB($color);
                }

                unset($options['setFontARGB']);
            }

            /* 设置单元格字体大小*/
            if (isset($options['fontSize']) && !empty($options['fontSize'])) {
                foreach ($options['fontSize'] as $sItem  =>  $size) {
                    $activeSheet->getStyle($sItem)
                        ->getFont()->setSize($size);
                }

                unset($options['fontSize']);
            }

            /* 设置单元格字体 */
            if (isset($options['fontName']) && !empty($options['fontName'])) {
                foreach ($options['fontName'] as $sItem  =>  $name) {
                    $activeSheet->getStyle($sItem)
                        ->getFont()->setName($name);
                }
                unset($options['fontName']);
            }

            /* 设置单元格文字自动换行 */
            if (isset($options['setWrapText']) && !empty($options['setWrapText'])) {
                foreach ($options['setWrapText'] as $sItem) {
                    $activeSheet->getStyle($sItem)->getAlignment()->setWrapText(true);
                }

                unset($options['setWrapText']);
            }

            /* 设置公式 */
            if (isset($options['formula']) && !empty($options['formula'])) {
                foreach ($options['formula'] as $fKey => $fItem) {
                    $activeSheet->setCellValue($fKey, $fItem);
                }

                unset($options['formula']);
            }

            /* 合并行列处理 */
            if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
                $activeSheet->setMergeCells($options['mergeCells']);
                unset($options['mergeCells']);
            }

            /* 设置居中 */
            if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                ];

                foreach ($options['alignCenter'] as $acItem) {
                    $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
                }

                unset($options['alignCenter']);
            }

            /* 设置加粗 */
            if (isset($options['bold']) && !empty($options['bold'])) {
                foreach ($options['bold'] as $bItem) {
                    $activeSheet->getStyle($bItem)->getFont()->setBold(true);
                }

                unset($options['bold']);
            }

            /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
            if (isset($options['setBorder']) && $options['setBorder']) {
                $border    = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color'       => ['argb' => 'FF000000'], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)->applyFromArray($border);
                unset($options['setBorder']);
            }

            /**
             * 设置表格信息
             *
             */
            if (isset($options['info']) && !empty($options['info'])) {
                $obj = $objSpreadsheet->getProperties();
                foreach ($options['info'] as $bItem => $v) {
                    call_user_func([$obj, "set".$bItem], $v);
                }
                unset($options['info'],$obj);
            }

            /*
             $spreadsheet->getProperties()
                ->setCreator("Helloweba")    //作者
                ->setLastModifiedBy("Yuegg") //最后修改者
                ->setTitle("Office 2007 XLSX Test Document")  //标题
                ->setSubject("Office 2007 XLSX Test Document") //副标题
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")  //描述
                ->setKeywords("office 2007 openxml php") //关键字
                ->setCategory("Test result file"); //分类
             */

            $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.' . $slowExportType);

            if (!isset($options['savePath'])) {
                /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header(
                    "Content-Disposition:attachment;filename=" . iconv(
                        "utf-8", "GB2312//TRANSLIT", $fileName
                    )
                );
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = time(). rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
            } else {
                $savePath = $options['savePath'] . "/".$fileName;
            }

            $objWriter = IOFactory::createWriter($objSpreadsheet, $exportType);
            $objWriter->save($savePath);
            /* 释放内存 */
            $objSpreadsheet->disconnectWorksheets();

            unset($objSpreadsheet);
            if (!isset($options['savePath'])) {
                ob_clean();
                ob_start();
                @readfile($savePath);
                unlink($savePath);
                ob_end_flush();
            }

            return ['code'=>0,'msg'=>'文件生成完成', 'data'=>['path'=>$savePath]];
        } catch (Exception $e) {
            return ['code'=>$e->getCode(),'msg'=>$e->getMessage(), 'data'=>[]];
        }
    }

    /**
     * 导出CSV
     * @param array  $datas      导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号']
     * @param string $fileName   导出文件名称
     * @param string $savePath   存储地址，如果不传则浏览器下载(直接输出到输出缓存)
     * @return bool
     */
    public static function exportCsv($datas,  $fileName = '', $savePath="") {
        $options = [];
        if ($savePath) {
            $options['savePath'] = $savePath;
        }
        return self::exportExcel($datas, $fileName, $options, "Csv");
    }

    /**
     * Excel导出，可导出多个sheet
     *
     * @param array  $datas      导出数据，格式[0=>['dataList'=>['A1' => 'XXXX公司报表', 'B1' => '序号'],'imgList'=>['C1'=>'图片在服务器的绝对地址']],1=>['dataList'=>['A1' => 'XXXX1公司报表', 'B1' => '序号1'],'imgList'=>['C1'=>'图片在服务器的绝对地址']]] key表示sheet Id
     * @param string $fileName   导出文件名称
     * @param array  $options    操作选项，例如：
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
     *                           array  setARGB     设置背景色，例如['A1'=>Color::COLOR_BLUE, 'C1'=>Color::COLOR_YELLOW]
     *                           array  setFontARGB  设置字体颜色  例如['A1'=>COLOR_BLUE ]
     *                           const COLOR_BLACK = 'FF000000';
     *                           const COLOR_WHITE = 'FFFFFFFF';
     *                           const COLOR_RED = 'FFFF0000';
     *                           const COLOR_DARKRED = 'FF800000';
     *                           const COLOR_BLUE = 'FF0000FF';
     *                           const COLOR_DARKBLUE = 'FF000080';
     *                           const COLOR_GREEN = 'FF00FF00';
     *                           const COLOR_DARKGREEN = 'FF008000';
     *                           const COLOR_YELLOW = 'FFFFFF00';
     *                           const COLOR_DARKYELLOW = 'FF808000';
     *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
     *                           array  rowHeight   设置行高  例如['1'=>100,'2'=>150]
     *                           array  setWrapText 设置文本自动换行  例如['A1','A2']
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式，整列设置，例如['A' => 'General']
     *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
     *                           array  bold        设置加粗样式，例如['A1', 'A2']
     *                           array  fontSize    设置单元格字体大小['A1'=>10,'A2'=>'20']
     *                           array  fontName    设置单元格字体['A1'=>'Arial']
     *                           string title       设置当前表格的标题 例：['title'=>'Scarecrow']
     *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
     *                           array  setSheetTitle 设置多个工作表的每个工作表名称 例如[0=>'工作表1', 1=>'工作表2'] KEY表示第几个工作表
     * @return bool
     */
    public static function exportMultipleSheetExcel($dataList,  $fileName = '', $options = [])
    {
        try {
            if (empty($dataList)) {
                return ['code'=>500,'msg'=>'导出数据不能为空', 'data'=>[]];
            }

            set_time_limit(0);
            ini_set('memory_limit', '1024M');
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = new Spreadsheet();
            /* 设置默认文字居左，上下居中 */
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
            ];
            $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);

            if (!array_key_exists('setSheetTitle', $options)) {
                $options['setSheetTitle'] = [];
            }
            foreach ($dataList as $sheetKey => $dataInfo) {
                if (!array_key_exists('dataList', $dataInfo) && !array_key_exists('imageDatas', $dataInfo)) {
                    $datas = $dataInfo;
                    $imageDatas = [];
                } else {
                    $datas = array_key_exists('dataList', $dataInfo) ? $dataInfo['dataList'] : [];
                    $imageDatas = array_key_exists('imgList', $dataInfo) ? $dataInfo['imgList'] : [];
                }

                if (empty($datas) && empty($imageDatas)) {
                    continue;
                }

                /* 设置Excel Sheet */
                $sheetTitle = array_key_exists($sheetKey, $options['setSheetTitle']) ? $options['setSheetTitle'][$sheetKey]: $sheetKey;
                if ($sheetKey == 0) {
                    $activeSheet = $objSpreadsheet->setActiveSheetIndex($sheetKey);
                    if (isset($options['setSheetTitle']) && is_array($options['setSheetTitle'])) {
                        $activeSheet->setTitle($sheetTitle);
                    }
                } else {
                    $activeSheet = new Worksheet($objSpreadsheet, $sheetTitle);
                    $objSpreadsheet->addSheet($activeSheet, $sheetKey);
                }

                /* 打印设置 */
                if (isset($options['print']) && $options['print']) {
                    /* 设置打印为A4效果 */
                    $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                    /* 设置打印时边距 */
                    $pValue = 1 / 2.54;
                    $activeSheet->getPageMargins()->setTop($pValue / 2);
                    $activeSheet->getPageMargins()->setBottom($pValue * 2);
                    $activeSheet->getPageMargins()->setLeft($pValue / 2);
                    $activeSheet->getPageMargins()->setRight($pValue / 2);
                }

                /* 行数据处理 */
                foreach ($datas as $sKey => $sItem) {
                    /* 默认文本格式 */
                    $pDataType = DataType::TYPE_STRING;

                    /* 设置单元格格式 */
                    if (isset($options['format']) && !empty($options['format'])) {
                        $colRow = Coordinate::coordinateFromString($sKey);

                        /* 存在该列格式并且有特殊格式 */
                        if (isset($options['format'][$colRow[0]]) &&
                            NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                            $activeSheet->getStyle($sKey)->getNumberFormat()
                                ->setFormatCode($options['format'][$colRow[0]]);

                            if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
                                is_numeric(str_replace(['￥', ','], '', $sItem))) {
                                /* 数字格式转换为数字单元格 */
                                $pDataType = DataType::TYPE_NUMERIC;
                                $sItem     = str_replace(['￥', ','], '', $sItem);
                            }
                        } elseif (is_int($sItem)) {
                            $pDataType = DataType::TYPE_NUMERIC;
                        }
                    }

                    $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);

                    /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
                    if (false !== strstr($sKey, ":")) {
                        $options['mergeCells'][$sKey] = $sKey;
                    }
                }

                if ($imageDatas) {
                    $extList = ['jpg','jpeg','gif', 'bmp', 'png', 'pdf', 'doc', 'docx', 'xlsx', 'xls'];
                    foreach ($imageDatas as $iKey => $iItem) {
                        if (!file_exists($iItem)) {
                            continue;
                        }
                        if (!in_array(strtolower(pathinfo($iItem, PATHINFO_EXTENSION)), $extList)) {
                            continue;
                        }
                        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
                        $drawing ->setName($iKey);
                        $drawing ->setPath($iItem);
                        $drawing ->setWidth(60);
                        $drawing ->setHeight(30);
                        $drawing ->setCoordinates($iKey);
                        $drawing ->setOffsetX(0);
                        $drawing ->setOffsetY(0);
                        $drawing ->setWorksheet($activeSheet);
                    }
                }

                /* 设置锁定行 */
                if (isset($options['freezePane']) && !empty($options['freezePane'])) {
                    $activeSheet->freezePane($options['freezePane']);
                }

                /* 设置宽度 */
                if (isset($options['setWidth']) && !empty($options['setWidth'])) {
                    foreach ($options['setWidth'] as $swKey => $swItem) {
                        $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
                    }
                }

                /*设置行高*/
                if (isset($options['rowHeight']) && !empty($options['rowHeight'])) {
                    foreach ($options['rowHeight'] as $swKey => $swItem) {
                        $activeSheet->getRowDimension($swKey)->setRowHeight($swItem);
                    }
                }

                /* 设置背景色 */
                if (isset($options['setARGB']) && !empty($options['setARGB'])) {
                    foreach ($options['setARGB'] as $sItem  =>  $color) {
                        $activeSheet->getStyle($sItem)
                            ->getFill()->setFillType(Fill::FILL_SOLID)
                            ->getStartColor()->setARGB($color);
                    }
                }

                /* 设置字体颜色*/
                if (isset($options['setFontARGB']) && !empty($options['setFontARGB'])) {
                    foreach ($options['setFontARGB'] as $sItem  =>  $color) {
                        $activeSheet->getStyle($sItem)
                            ->getFont()->getColor()->setARGB($color);
                    }
                }

                /* 设置单元格字体大小*/
                if (isset($options['fontSize']) && !empty($options['fontSize'])) {
                    foreach ($options['fontSize'] as $sItem  =>  $size) {
                        $activeSheet->getStyle($sItem)
                            ->getFont()->setSize($size);
                    }
                }

                /* 设置单元格字体 */
                if (isset($options['fontName']) && !empty($options['fontName'])) {
                    foreach ($options['fontName'] as $sItem  =>  $name) {
                        $activeSheet->getStyle($sItem)
                            ->getFont()->setName($name);
                    }
                }

                /* 设置单元格文字自动换行 */
                if (isset($options['setWrapText']) && !empty($options['setWrapText'])) {
                    foreach ($options['setWrapText'] as $sItem) {
                        $activeSheet->getStyle($sItem)->getAlignment()->setWrapText(true);
                    }
                }


                /* 设置公式 */
                if (isset($options['formula']) && !empty($options['formula'])) {
                    foreach ($options['formula'] as $fKey => $fItem) {
                        $activeSheet->setCellValue($fKey, $fItem);
                    }
                }

                /* 合并行列处理 */
                if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
                    $activeSheet->setMergeCells($options['mergeCells']);
                }

                /* 设置居中 */
                if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
                    $styleArray = [
                        'alignment' => [
                            'horizontal' => Alignment::HORIZONTAL_CENTER,
                            'vertical'   => Alignment::VERTICAL_CENTER,
                        ],
                    ];

                    foreach ($options['alignCenter'] as $acItem) {
                        $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
                    }
                }

                /* 设置加粗 */
                if (isset($options['bold']) && !empty($options['bold'])) {
                    foreach ($options['bold'] as $bItem) {
                        $activeSheet->getStyle($bItem)->getFont()->setBold(true);
                    }
                }

                /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
                if (isset($options['setBorder']) && $options['setBorder']) {
                    $border    = [
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => Border::BORDER_THIN, // 设置border样式
                                'color'       => ['argb' => 'FF000000'], // 设置border颜色
                            ],
                        ],
                    ];
                    $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                    $activeSheet->getStyle($setBorder)->applyFromArray($border);
                }
            }

            /**
             * 设置表格信息
             *
             */
            if (isset($options['info']) && !empty($options['info'])) {
                $obj = $objSpreadsheet->getProperties();
                foreach ($options['info'] as $bItem => $v) {
                    call_user_func([$obj, "set".$bItem], $v);
                }
                unset($options['info'],$obj);
            }

            /*
             $spreadsheet->getProperties()
                ->setCreator("Helloweba")    //作者
                ->setLastModifiedBy("Yuegg") //最后修改者
                ->setTitle("Office 2007 XLSX Test Document")  //标题
                ->setSubject("Office 2007 XLSX Test Document") //副标题
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")  //描述
                ->setKeywords("office 2007 openxml php") //关键字
                ->setCategory("Test result file"); //分类
             */

            $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

            if (!isset($options['savePath'])) {
                /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header(
                    "Content-Disposition:attachment;filename=" . iconv(
                        "utf-8", "GB2312//TRANSLIT", $fileName
                    )
                );
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = time(). rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
            } else {
                $savePath = $options['savePath'] . "/".$fileName;
            }
            $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
            $objWriter->save($savePath);
            /* 释放内存 */
            $objSpreadsheet->disconnectWorksheets();

            unset($objSpreadsheet);
            if (!isset($options['savePath'])) {
                ob_clean();
                ob_start();
                @readfile($savePath);
                unlink($savePath);
                ob_end_flush();
            }

            return ['code'=>0,'msg'=>'文件生成完成', 'data'=>['path'=>$savePath]];
        } catch (Exception $e) {
            return ['code'=>$e->getCode(),'msg'=>$e->getMessage()];
        }
    }

    /**
     * 导入数据，支持xlsx/xls/csv文件导入,他会自动根据文件后缀判定使用什么格式读取
     *
     * @param string $file      文件地址
     * @param int    $sheet     工作表sheet(传0则获取第一个sheet)
     * @param int    $maxRow   判断文件最大行数
     * @param int    $maxColumn 列数(传0则自动获取最大列)
     * @param array  $options   操作选项
     *                          array formula    公式数组
     *                          array format     单元格格式数组
     *                          array isUploadImage 是否上传图片  注意只支持Xlsx格式文件上传图片
     *                          array uploadFileToPath 上传图片到指定路径
     * @param boolean $isThrowError 是否抛出异常
     * @return array
     */
    public static function importFileData($file = '', $sheet = 0, $maxRow=0, $maxColumn = 0, $options = [],  $isThrowError=false)
    {
        $fileExtInfo = pathinfo($file);
        if (!array_key_exists("extension", $fileExtInfo)) {
            if ($isThrowError) {
                throw new \Exception('导入文件地址不正确');
            } else {
                return [
                    'code'  =>  2,
                    'msg'   =>  '导入文件地址不正确!'
                ];
            }
        }
        $slowFileExt = strtolower($fileExtInfo['extension']);
        $fileExt = ucfirst($slowFileExt);
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) OR !file_exists($file)) {
                if ($isThrowError) {
                    throw new \Exception('文件不存在!');
                } else {
                    return [
                        'code'  =>  1,
                        'msg'   =>  '文件不存在!'
                    ];
                }
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader($fileExt);

            if (!$objRead->canRead($file)) {
                if ($isThrowError) {
                    throw new \Exception("不支持{$slowFileExt}格式文件导入");
                } else {
                    return [
                        'code'  =>  2,
                        'msg'   =>  "不支持{$slowFileExt}格式文件导入"
                    ];
                }
            }

            /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel对象 */
            $obj = $objRead->load($file);
            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (0 == $maxColumn) {
                /* 取得最大的列号 */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原逻辑，循环时使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data   = [];

            if ($maxRow > 0 && $maxRow < $rowCnt) {
                if ($isThrowError) {
                    throw new \Exception('文件数据行超过了最大值');
                } else {
                    return [
                        'code'  =>  3,
                        'msg'   =>  '文件数据行超过了最大值'
                    ];
                }
            }

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId   = $cellName . $_row;
                    $cell     = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                    if (!empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }

                /* 判断是否整行数据为空，是的话删除该行数据 */
                if ($isNull) {
                    unset($data[$_row]);
                }
            }

            if ($slowFileExt === 'xlsx' && array_key_exists('isUploadImage', $options) && $options['isUploadImage']) {
                $imageFilePath = array_key_exists('uploadFileToPath', $options) ? $options['uploadFileToPath'] : "./uploads";
                $imageFilePath = rtrim($imageFilePath, '/') . '/';
                if (!is_dir($imageFilePath)) {
                    self::CreateDir($imageFilePath);
                }
                foreach ($currSheet->getDrawingCollection() as $drawing) {
                    list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
                    $imageFileName = $drawing->getCoordinates() . mt_rand(1000, 9999);
                    switch ($drawing->getExtension()) {
                        case 'jpg':
                        case 'JPG':
                        case 'jpeg':
                        case 'JPEG':
                            $imageFileName .= '.jpg';
                            $source = imagecreatefromjpeg($drawing->getPath());
                            imagejpeg($source, $imageFilePath . $imageFileName);
                            break;
                        case 'gif':
                        case 'GIF':
                            $imageFileName .= '.gif';
                            $source = imagecreatefromgif($drawing->getPath());
                            imagegif($source, $imageFilePath . $imageFileName);
                            break;
                        case 'png':
                        case 'PNG':
                            $imageFileName .= '.png';
                            $source = imagecreatefrompng($drawing->getPath());
                            imagepng($source, $imageFilePath . $imageFileName);
                            break;
                    }

                    $data[$startRow][$startColumn] = $imageFilePath . $imageFileName;
                }
            }

            return [
                'code'  =>  0,
                'msg'   =>  "",
                'data'  =>  $data
            ];
        } catch (\Exception $e) {
            if ($isThrowError) {
                throw new \Exception($e->getMessage());
            } else {
                return [
                    'code'  =>  4,
                    'msg'   =>  $e->getMessage(),
                    'data'  =>  []
                ];
            }
        }

    }

    /**
     * 二维数组转换为导出Excel、CSV的数据格式
     * @param datas 转换的数据，数据格式为二维数组,[['title1','title2'...],['content1','content2'...],['content1','content2'...]...],其中每个子数组代表一行数据
     */
    public static function dataToExcelData($datas)
    {
        $tempData = [];
        foreach ($datas as $i => $v) {
            foreach ($v as $j => $jv) {
                $tempData[self::IntToChr($j).($i+1)] = $datas[$i][$j];
            }
        }
        return $tempData;
    }

    /**
     * 数字转字母 （类似于Excel列标）
     * @param Int $index 索引值
     * @param Int $start 字母起始值
     * @return String 返回字母
     */
    public static function IntToChr($index, $start = 65) {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= self::IntToChr(floor($index / 26)-1);
        }
        return $str . chr($index % 26 + $start);
    }

    /**
     * 创建文件夹
     * @param $dirPath
     * @param int $chmod
     * @return bool
     */
    public static function CreateDir($dirPath, $chmod = 0777)
    {
        try {
            return is_dir($dirPath) or (self::CreateDir(dirname($dirPath), $chmod) && mkdir($dirPath, $chmod) && chmod($dirPath, $chmod));
        } catch (\Throwable $exception) {
            return false;
        }
    }
}